﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using PMPUtils.Resources;
using PMPUtils.Goals;
using PMPUtils.DB;

namespace PMPUtils.Report
{
    public class DailyUpdateOperator
    {
        public static DataTable ReadFinalReviews(String sprint)
        {
            String resourceList = ResourceOperator.ReadResouceList("'NF1', 'NF2', 'OPTM'");
            String priorityClause = GoalsOperator.ReadPriorityClause(sprint);

            String strQuery = " select " +
                " datediff(day, max(os_historystep.finish_date), getdate()) + case when datediff(hour, max(os_historystep.finish_date), getdate()) > 0 then 1 else 0 end as 'In Review(Days)', " +
                priorityClause + " as Priority, " +
                " customfieldvalue.stringvalue 'Reviewer', " +
                " issuetype.pname 'Type', " +
                " jiraissue.pkey JIRA, " +
                " jiraissue.assignee " +
                " from jiraissue " +
                " left outer join issuelink on (issuelink.destination = jiraissue.id and issuelink.linktype = '10000') " +
                " left outer join jiraissue story on (issuelink.linktype = '10000' and issuelink.source = story.id) " +
                " join customfieldvalue on (customfieldvalue.issue = jiraissue.id and customfieldvalue.customfield = '10000') " +
                " join issuetype on (issuetype.id = jiraissue.issuetype) " +
                " join issuestatus on (issuestatus.id = jiraissue.issuestatus) " +
                " join priority on (jiraissue.priority = priority.id)  " +
                " join os_historystep on (    os_historystep.entry_id = jiraissue.workflow_id and os_historystep.action_id in ('791', '831', '911')) " +
                " where jiraissue.assignee in (" + resourceList + ") " +
                " and customfieldvalue.stringvalue not in (" + resourceList + ") " +
                " and issuestatus.pname in ('Review') " +
                " group by " +
                priorityClause + ", " +
                " customfieldvalue.stringvalue, issuetype.pname,substring(jiraissue.summary, 0, 25), jiraissue.assignee, jiraissue.pkey " +
                " order by " +
                priorityClause + ", " +
                " customfieldvalue.stringvalue, " +
                " datediff(day, max(os_historystep.finish_date), getdate()) + case when datediff(hour, max(os_historystep.finish_date), getdate()) > 0 then 1 else 0 end desc";
            DBOperator dbOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
            DataTable dtFinalReviews = dbOperator.Read(strQuery);

            return dtFinalReviews;
        }

        public static DataTable ReadNMI(String sprint)
        {
            String resourceList = ResourceOperator.ReadResouceList("'NF1', 'NF2', 'OPTM'");
            String priorityClause = GoalsOperator.ReadPriorityClause(sprint);

            String strQuery = " select " +
                "  datediff(day, max(os_historystep.finish_date), getdate()) + case when datediff(hour, max(os_historystep.finish_date), getdate()) > 0 then 1 else 0 end as 'In Need More Info(Days)', " +
                "  customfieldvalue.stringvalue 'Reporter', " +
                priorityClause + " as Priority, " +
                "  issuetype.pname 'Type', " +
                "  jiraissue.pkey JIRA, " +
                "  jiraissue.assignee " +
                "  from jiraissue " +
                "  left outer join issuelink on (issuelink.destination = jiraissue.id and issuelink.linktype = '10000') " +
                "  left outer join jiraissue story on (issuelink.linktype = '10000' and issuelink.source = story.id) " +
                "  join customfieldvalue on (customfieldvalue.issue = jiraissue.id and customfieldvalue.customfield = '10000') " +
                "  join issuetype on (issuetype.id = jiraissue.issuetype) " +
                "  join issuestatus on (issuestatus.id = jiraissue.issuestatus) " +
                "  join priority on (jiraissue.priority = priority.id)  " +
                "  join os_historystep on (    os_historystep.entry_id = jiraissue.workflow_id and os_historystep.action_id in ('761', '901')) " +
                "  where jiraissue.assignee in (" + resourceList + ") " +
                "  and issuestatus.pname in ('Need More Information', 'Missing Details') " +
                "  group by " +
                priorityClause + ", " +
                "  customfieldvalue.stringvalue, issuetype.pname,substring(jiraissue.summary, 0, 25), jiraissue.assignee, jiraissue.pkey " +
                "  union " +
                "   select " +
                "  datediff(day, max(os_historystep.finish_date), getdate()) + case when datediff(hour, max(os_historystep.finish_date), getdate()) > 0 then 1 else 0 end as 'In Need More Info(Days)', " +
                "  customfieldvalue.stringvalue 'Reporter', " +
                priorityClause + " as Priority, " +
                "  issuetype.pname 'Type', " +
                "  jiraissue.pkey JIRA, " +
                "  jiraissue.assignee " +
                "  from jiraissue " +
                "  left outer join issuelink on (issuelink.destination = jiraissue.id and issuelink.linktype = '10000') " +
                "  left outer join jiraissue story on (issuelink.linktype = '10000' and issuelink.source = story.id) " +
                "  join customfieldvalue on (customfieldvalue.issue = jiraissue.id and customfieldvalue.customfield = '10000') " +
                "  join issuetype on (issuetype.id = jiraissue.issuetype) " +
                "  join issuestatus on (issuestatus.id = jiraissue.issuestatus) " +
                "  join priority on (jiraissue.priority = priority.id)  " +
                "  join os_historystep on (    os_historystep.entry_id = jiraissue.workflow_id and os_historystep.action_id in ('761', '901')) " +
                "  where jiraissue.assignee in (" + resourceList + ") " +
                "  and issuestatus.pname in ('Need More Information', 'Missing Details') " +
                "  group by " +
                priorityClause + ", " +
                "  customfieldvalue.stringvalue, issuetype.pname,substring(jiraissue.summary, 0, 25), jiraissue.assignee, jiraissue.pkey " +
                "  order by " +
                priorityClause + ", " +
                "  customfieldvalue.stringvalue, " +
                "  datediff(day, max(os_historystep.finish_date), getdate()) + case when datediff(hour, max(os_historystep.finish_date), getdate()) > 0 then 1 else 0 end desc ";
            DBOperator dbOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
            DataTable dtNMI = dbOperator.Read(strQuery);

            return dtNMI;
        }

        public static DataTable ReadPeerReview(String sprint)
        {
            String resourceList = ResourceOperator.ReadResouceList("'NF1', 'NF2', 'OPTM'");
            String priorityClause = GoalsOperator.ReadPriorityClause(sprint);

            String strQuery = " select distinct " +
                priorityClause + " as StoryPriority, " +
                " customfieldvalue.stringvalue Reviewer, " +
                " priority.pname Priority, " +
                " issuetype.pname 'Type', " +
                " issuestatus.pname Status, " +
                " jiraissue.pkey JIRA, " +
                " substring(jiraissue.summary, 0, 25) Description, " +
                " jiraissue.assignee " +
                " from jiraissue " +
                " left outer join issuelink on (issuelink.destination = jiraissue.id and issuelink.linktype = '10000') " +
                " left outer join jiraissue story on (issuelink.linktype = '10000' and issuelink.source = story.id) " +
                " join customfieldvalue on (customfieldvalue.issue = jiraissue.id and customfieldvalue.customfield = '10000') " +
                " join issuetype on (issuetype.id = jiraissue.issuetype) " +
                " join issuestatus on (issuestatus.id = jiraissue.issuestatus) " +
                " join priority on (jiraissue.priority = priority.id)  " +
                " where jiraissue.assignee in (" + resourceList + ") " +
                " and customfieldvalue.stringvalue in (" + resourceList + ") " +
                " and issuestatus.pname in ('Review', 'Needs Commit', 'Needs Merge')";

            DBOperator dbOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
            DataTable dtPeerReview = dbOperator.Read(strQuery);

            return dtPeerReview;
        }

        public static DataTable ReadReopen(String sprint)
        {
            String resourceList = ResourceOperator.ReadResouceList("'NF1', 'NF2', 'OPTM'");
            String priorityClause = GoalsOperator.ReadPriorityClause(sprint);

            String strQuery = " select distinct " +
                priorityClause + " as StoryPriority, " +
                " customfieldvalue.stringvalue Reviewer, " +
                " priority.pname Priority, " +
                " issuetype.pname 'Type', " +
                " issuestatus.pname Status, " +
                " jiraissue.pkey JIRA, " +
                " substring(jiraissue.summary, 0, 25) Description, " +
                " jiraissue.assignee " +
                " from jiraissue " +
                " left outer join issuelink on (issuelink.destination = jiraissue.id and issuelink.linktype = '10000') " +
                " left outer join jiraissue story on (issuelink.linktype = '10000' and issuelink.source = story.id) " +
                " join customfieldvalue on (customfieldvalue.issue = jiraissue.id and customfieldvalue.customfield = '10000') " +
                " join issuetype on (issuetype.id = jiraissue.issuetype) " +
                " join issuestatus on (issuestatus.id = jiraissue.issuestatus) " +
                " join priority on (jiraissue.priority = priority.id)  " +
                " where jiraissue.assignee in (" + resourceList + ") " +
                " and issuestatus.pname in ('Reopened')";

            DBOperator dbOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
            DataTable dtReopen = dbOperator.Read(strQuery);

            return dtReopen;
        }
    }
}
